package DBLayer;

import java.util.ArrayList;
import ModelLayer.*;
import java.sql.*;

public class DbSalesLine implements IFDBSalesLine {
	private Connection con;
	private DbProduct dbPro;
	
	public DbSalesLine()
	{
		con = DbConnection.getInstance().getDBcon();
		dbPro = new DbProduct();
	}

	@Override
	public ArrayList<SalesLine> getAllSalesLines(int salesOrderId)
	{
		String wClause = " WHERE orderID = '" + salesOrderId + "'";		
		return miscWhere(wClause);
	}

	@Override
	public SalesLine searchSalesLine(int saleOrderId, int productId) {
		
		String wClause = " orderID = '" + saleOrderId + "' AND productID = '" + productId + "'";
		return singleWhere(wClause);
	}

	public int insertSalesLine(SalesLine salesLine, int saleOrderId) throws Exception {
		//call to get the next id number
		int rc = -1;
		String query="INSERT INTO SalesLine(orderID, productID, quantity)  VALUES('"+
				saleOrderId  + "','"  +
				salesLine.getP().getId()  + "','"  +
				salesLine.getQuantity() + "')";

		System.out.println("insert : " + query);
		try{ // insert new salesLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("SalesLine not created");
			throw new Exception ("SalesLine is not inserted correct");
		}
		return(rc);
	}

	public int updateSalesLine(SalesLine salesLine, int saleOrderId) {
		int rc=-1;

		String query="UPDATE SalesLine SET "+
				"quantity ='"+ salesLine.getQuantity() + "', " +
				" WHERE orderID = '"+ saleOrderId + "' AND productID = '" + salesLine.getP().getId() + "'";
		System.out.println("Update query:" + query);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in salesLine db: "+ex);
		}
		return(rc);
	}

	public int deleteSalesLine(int saleOrderId) {
		int rc=-1;

		String query="DELETE FROM SalesLine WHERE orderID = '" +
				saleOrderId + "'";
		System.out.println(query);
		try{ // delete from salesLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in SalesLine db: "+ex);
		}
		return(rc);
	}
	
	private ArrayList<SalesLine> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<SalesLine> list = new ArrayList<SalesLine>();	

		String query =  buildQuery(wClause);

		try{ // read the salesLine from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while(results.next()){
				SalesLine salesLineObj = new SalesLine();
				salesLineObj = buildPartOrder(results);	
				list.add(salesLineObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	private SalesLine singleWhere(String wClause)
	{
		ResultSet results;
		SalesLine salesLineObj = new SalesLine();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the SalesLine from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				salesLineObj = buildPartOrder(results);
				stmt.close();
			}
			else{ //no SalesLine was found
				salesLineObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return salesLineObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query="SELECT productID, quantity FROM SalesLine";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	
	private SalesLine buildPartOrder(ResultSet results)
	{   SalesLine salesLineObj = new SalesLine();
		try{ // the columns from the table salesLine are used
			salesLineObj.setQuantity(results.getInt("quantity"));		
			salesLineObj.setP(dbPro.searchProductById(results.getInt("productId")));
		}
		catch(Exception e)
		{
			System.out.println("error in building the SalesLine object");
		}
		return salesLineObj;
	}
}
	
	
